package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import edu.byu.intex2.data.GUID;
import edu.byu.intex2.data.bo.UsedCar;
import java.sql.*;


/**
 * UsedCar Data Access Object
 * @author Jason Whiting
 */
public class UsedCarDAO
{

    /////////////////////////////////////////////
    ///   Singleton code
    private static UsedCarDAO instance = null;

    /** Creates a new instance of UsedCarDAO */
    private UsedCarDAO()
    {
    }//constructor

    /** Retrieves the single instance of this class */
    public static synchronized UsedCarDAO getInstance()
    {
        if (instance == null)
        {
            instance = new UsedCarDAO();
        }
        return instance;
    }//getInstance

    ////////////////////////////////////////////
    ///   CREATE methods
    /** Creates a new UsedCar */
    public UsedCar create(String id) throws DataException
    {
        // Create new UsedCar with Guid
        UsedCar usedCar = new UsedCar(id);

        // If id is null, then generate a new guid.  I see no reason for this, but included it because of TA's logic.
        if (id == null)
        {
            usedCar.setId(GUID.generate());
        }

        usedCar.setObjectAlreadyInDB(false); // set alreadyInDB to false

        // put UsedCar in cache
        Cache.getInstance().put(usedCar.getId(), usedCar);
        return usedCar;
    }//create


    ////////////////////////////////////////////
    ///   READ methods
    /** Reads an existing UsedCar from the database   */
    public UsedCar read(String id) throws DataException
    {
        // Check Cache
        if (Cache.getInstance().containsKey(id))
        {
            return (UsedCar) Cache.getInstance().get(id);
        }

        // Get Connection
        Connection conn = ConnectionPool.getInstance().get();

        // Try Reading with connection
        try
        {
            return read(id, conn);
        } catch (Exception e)
        {
            throw new DataException("An error occurred while reading the business object information.", e);
        } finally
        {
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to read an existing UsedCar from the database    */
    synchronized UsedCar read(String id, Connection conn) throws Exception
    {
        // Check Cache
        if (Cache.getInstance().containsKey(id))
        {
            return (UsedCar) Cache.getInstance().get(id);
        }

        // Prepare a SQL Statement
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM used_car where id=?");

        try
        {
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next())
            {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("UsedCar with id '" + id + "' not found.");
        } finally
        {
            stmt.close();
        }
    }//read

    /**
     * Internal method to create a UsedCar object from a record
     * @return usedCar
     */
    synchronized UsedCar readRecord(ResultSet rs, Connection conn) throws Exception
    {
        // Check cache for object
        if (Cache.getInstance().containsKey(rs.getString("id")))
        {
            return (UsedCar) Cache.getInstance().get(rs.getString("id"));
        }

        // Instantiate new usedCar
        UsedCar usedCar = new UsedCar(rs.getString("id"));

        // Object is already in database
        usedCar.setObjectAlreadyInDB(true);

        // Set each attribute to object
        usedCar.setCarCondition(rs.getString("car_condition"));
        usedCar.setMileage(rs.getInt("mileage"));

        // Set PhysicalCar values
        PhysicalCarDAO.getInstance().setPhysicalCarValues(usedCar, conn);

        // Object is not dirty
        usedCar.setDirty(false);

        // Put object in cache
        Cache.getInstance().put(usedCar.getId(), usedCar);

        return usedCar;
    }//readRecord

    /////////////////////////////////////////////
    ///   UPDATE methods
    /** Saves an existing usedCar in the database */
    public void save(UsedCar usedCar) throws DataException
    {
        // get a connection from the pool, save all of the sales first using the other usedCar method and then commit them in the database
        Connection conn = ConnectionPool.getInstance().get();

        try
        {
            save(usedCar, conn); // save with connection
            conn.commit();  // commit the db transaction
        } catch (Exception e)
        {
            try
            {
                //rollback if there were any errors saving, catch any other errors you might have
                conn.rollback();
            } catch (SQLException e2)
            {
                throw new DataException("Could not roll back the usedCar database transaction!", e2);
            }
            throw new DataException("An error occurred while saving the usedCar object information.", e);
        } finally
        {
            // release the connection
            ConnectionPool.getInstance().release(conn);
        }
    }

    /** Internal method to update a usedCar in the database */
    void save(UsedCar usedCar, Connection conn) throws Exception
    {
        // put the usedCar object to cache
        Cache.getInstance().put(usedCar.getId(), usedCar);

        // decide whether to update or insert into the database
        if (usedCar.isObjectAlreadyInDB() && usedCar.isDirty())
        {
            // Update PhysicalCar
            PhysicalCarDAO.getInstance().saveNoCommit(usedCar, conn);

            // Update UsedCar
            update(usedCar, conn);
        } else if (usedCar.isDirty())
        {
            // Insert PhysicalCar
            PhysicalCarDAO.getInstance().saveNoCommit(usedCar, conn);

            // Insert UsedCar
            insert(usedCar, conn);
        }//if
    }

    /** External method to update or create a physicalCar in the database without committing connection */
    public void saveNoCommit(UsedCar usedCar, Connection conn) throws Exception
    {
        Boolean alreadyInDB = usedCar.isObjectAlreadyInDB();

        Boolean pcInDB = PhysicalCarDAO.getInstance().isInDB(usedCar.getId());

        // decide whether to update or insert into the database
        if (alreadyInDB)
        {
            // Update Physical Car
            PhysicalCarDAO.getInstance().saveNoCommit(usedCar, conn);

            // Update Used Car
            update(usedCar, conn);
        } else
        {
            // Update Physical Car
            PhysicalCarDAO.getInstance().saveNoCommit(usedCar, conn);

            // Insert Used Car
            insert(usedCar, conn);

            // If physicalCar is not in database... it now is... so set its alreadyInDB to true -- I don't think this is necessary.  insert is already setting this
            if (pcInDB == true)
            {
                usedCar.setObjectAlreadyInDB(true);
            }
        }
    }

    /** Saves an existing usedCar to the database */
    private void update(UsedCar usedCar, Connection conn) throws Exception
    {
        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("UPDATE used_car SET car_condition=?, mileage=?  WHERE id=?");
        try
        {
            // Get Person Attributes and add them to the statement
            stmt.setString(1, usedCar.getCarCondition());
            stmt.setInt(2, usedCar.getMileage());
            stmt.setString(3, usedCar.getId());
            stmt.execute();

            // Person is clean!
            usedCar.setDirty(false);
        } finally
        {
            stmt.close();  // make sure to close the prepared statement
        }
    }

    /** Inserts a new usedCar into the database */
    private void insert(UsedCar usedCar, Connection conn) throws Exception
    {
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO used_car (id, car_condition, mileage) VALUES (?, ?, ?)");
        try
        {
            // Set UsedCar Attributes
            stmt.setString(1, usedCar.getId());
            stmt.setString(2, usedCar.getCarCondition());
            stmt.setInt(3, usedCar.getMileage());
            stmt.execute();

            usedCar.setObjectAlreadyInDB(true); // now in DB
            usedCar.setDirty(false); // object is clean
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            stmt.close(); // close the prepared statement
        }
    }

    /////////////////////////////////////////////////
    ///   DELETE methods
    /** We do not support deleting of business objects in this application */
    public void delete(UsedCar usedCar) throws DataException
    {
        throw new RuntimeException("Nice try. The delete function is not supported in this application.");
    }


    ////////////////////////////////////////////////
    ///   SEARCH methods
    /*
     * Get the Physical Car from the database based on the VIN
     * @param VIN of physical car to find.
     */
    public UsedCar getByVIN(String VIN) throws Exception
    {
        // Grab a connection
        Connection conn = ConnectionPool.getInstance().get();

        // use a prepared statement to prevent sql injection
        PreparedStatement stmt = conn.prepareStatement("SELECT * FROM used_car, physical_car WHERE vin=? AND used_car.id = physical_car.id");
        try
        {
            stmt.setString(1, VIN);
            ResultSet rs = stmt.executeQuery(); // execute query
            if (rs.next())
            {
                // Read the record with the result set
                return readRecord(rs, conn);
            }//if
            throw new DataException("Physical_Car with VIN '" + VIN + "' not found.");
        } finally
        {
            stmt.close();
            ConnectionPool.getInstance().release(conn);  // release the connection
        }
    }
}
